VB Sample Code for exporting a report

Exporting reports using the Crystal Reports Print Engine in Visual Basic has caused a problem for several developers. This is not so much a problem with Crystal Reports or Visual Basic, but instead, is an issue on how memory allocation is handled by each application. To understand why this causes a problem, we must look at how the PEExportOptions structure is declared in C and in Visual Basic.

The C declaration for the PEExportOptions structure is as follows:

typedef struct PEExportOptions
{
    WORD StructSize; 
    char formatDLLName [PE_DLL_NAME_LEN];
    DWORD formatType;
    void FAR *formatOptions;
    char destinationDLLName [PE_DLL_NAME_LEN];
    DWORD destinationType;
    void FAR *destinationOptions;
    // Following 2 member are Set by PEGetExportOptions, but ignored by 
PEExportTo
    WORD nFormatOptionsBytes;
    WORD nDestinationOptionsBytes;
} PEExportOptions;

Since Visual Basic does not support pointers as data members of structures, the equivalent of "void FAR *" is "Long"

The Visual Basic declaration for the same structure is as follows:

Type PEExportOptions
    StructSize As Integer
    FormatDLLName As String * PE_DLL_NAME_LEN
    FormatType As Long
    FormatOptions As Long
    DestinationDLLName As String * PE_DLL_NAME_LEN
    DestinationType As Long
    DestinationOptions As Long
    ' Following 2 member are Set by PEGetExportOptions, but ignored by 
PEExportTo
    NFormatOptionsBytes As Integer
    NDestinationOptionsBytes As Integer
End Type

The FormatOptions and DestinationOptions members of the PEExportOptions structure, depending on the format and destination selected, will point to different structures (ie. DiskOptions, MAPIOptions, etc). There is no easy way to get the address for any variable in Visual Basic. This means that structures with members that are pointers cannot be used in Visual Basic. This problem exists in 16 and 32bit Visual Basic, but the problem is more severe in 32bit. The Crystal Reports Print Engine ( CRPE32.DLL) uses 1-byte alignment. Visual Basic 32bit uses 4-byte alignment.

In the example below, assume that each space between a vertical separator is a byte. Assume that X and Y are members of the PEExportOptions structure. Now assume that X takes up 2 bytes and Y takes up 2 bytes:

1-byte alignment (CRPE.DLL / CRPE32.DLL)

|X |X |Y |Y |   |   |   |   |

4-byte alignment (Visual Basic)

|X |X |   |   |Y |Y |   |   |

Notice the space in the 4-byte alignment. This is how memory looks when a variable is declared in Visual Basic of type PEExportOptions. When the structure is passed to the Crystal Reports Engine, the Report Engine expects Y to be immediately after X, but as this is not the case, the values being set for exporting the report are not accurate and as a result, will fail.

There is good news. Crystal Reports has created a "Wrapper" DLL (Dynamic Link Library) that bypasses Visual Basic and allows the Report Engine to fill in the values for the PEExportOptions structure. This DLL did not exist prior to version 4.5. The DLL names in existing versions of Crystal Reports are as follows:

  1.         Download wrapper.zip and follow the readme.txt for usage

4.5 32bit "Vbexpt32.dll" (Download from web site, developed after Crystal Reports 4.5 shipped)

http://support.seagatesoftware.com/updates

  1.         Click on the patches/updates link on the orange menu bar.
  2.         Click on BBS files (FTP Link) link
  3.         Click on 32bit link
  4.         Download vbexp32.zip and follow the readme.txt for usage

5.0 16bit "Crwrap16.dll" (Shipped with Crystal Reports 5.0)

5.0 32bit "Crwrap32.dll" (Shipped with Crystal Reports 5.0)

6.0 16bit "Crwrap16.dll" (Shipped with Crystal Reports 6.0)

6.0 32bit "Crwrap32.dll" (Shipped with Crystal Reports 6.0)

If you are using Crystal Reports 4.5 16bit, include "wrapper.bas" in your Visual Basic project. If you are using 4.5 32bit Crystal Reports, include "vbexpt32.bas" in your Visual Basic project. If you are using Crystal Reports version 5.0 or higher, include "crwrap.bas" in your Visual Basic project.

Exporting using the Wrapper DLL is broken into the following API calls. These API calls are not found in any help file and are only documented in the "*.bas" file that you include in your project as mentioned above. The function declarations for these API calls and the format type constants are included in the "*.bas" file.

crPEExportToDisk - Exporting to Disk file
crPEExportToMAPI - Exporting to Mail (MAPI)
crPEExportToEXCH - Exporting to Microsoft Exchange
crPEExportToODBC - Exporting to an ODBC data source
crPEExportToHTML - Exporting to HTML format

Note:     Any strings being passed to these functions MUST be NULL terminated.

Here is some sample code from a Visual Basic application that demonstrates how to export using these wrapper functions. Note that you don't have to fill in each variable specified, since some variables are specific to the format being exported to. The case numbers used for ExportFormat and ExportDestination are specific to this example, and are used in this manner for clarity. You can change these values if you desire.

Dim Result As Integer
Dim MainJob As Integer
Dim ExportFormat As Integer
Dim ExportDestination As Integer
Dim FormatDLLName As String
Dim FormatType As Long
Dim UseSameNumberFormat As Integer
Dim UseSameDateFormat As Integer
Dim StringDelimiter As String
Dim FieldDelimiter As String
Dim DataSource As String
Dim UserID As String
Dim Password As String
Dim TableName As String
Dim MAPIToList As String
Dim MAPICCList As String
Dim MAPISubject As String
Dim MAPIMessage As String
Dim EXCHProfile As String
Dim EXCHPassword As String
Dim EXCHFolderPath As String

ExportFormat = Select an export format
ExportDestination = Select an export destination

DataSource = ODBC data source name
UserID = Logon user id
Password = Logon password
TableName = Table name to export data to

UseSameNumberFormat = 1 or 0 for True or False
UseSameDateFormat = 1 or 0 for True or False
StringDelimiter = String delimiter for character separated value format
FieldDelimiter = Field delimiter for character separated value format

MAPIToList = Email To list
MAPICCList = Email CC list
MAPISubject = Email subject
MAPIMessage = Email message

EXCHProfile = Exchange profile
EXCHPassword = Exchange password
EXCHFolderPath = Exchange folder path

' Open print engine
Result = PEOpenEngine()
' Open the selected report
MainJob = PEOpenPrintJob("c:\myreport.rpt" & vbNullChar)
' Set FormatDLLName and FormatType based on selected format type
Select Case ExportFormat
    Case 0    ' Crystal Reports
        FormatDLLName = "uxfcr.dll"        ' "u2fcr.dll" for 32bit
        FormatType = crUXFCrystalReportType
    Case 1    ' Data Interchange
        FormatDLLName = "uxfdif.dll"        ' "u2fdif.dll" for 32bit
        FormatType = crUXFDIFType
    Case 2    ' Word for Windows
        FormatDLLName = "uxfwordw.dll"    ' "u2fwordw.dll" for 32bit
        FormatType = crUXFWordWinType
    Case 3    ' Record Style
        FormatDLLName = "uxfrec.dll"        ' "u2frec.dll" for 32bit
        FormatType = crUXFRecordType
    Case 4    ' Rich Text
        FormatDLLName = "uxfrtf.dll"        ' "u2frtf.dll" for 32bit
        FormatType = crUXFRichTextFormatType
    Case 5    ' Comma Separated Values
        FormatDLLName = "uxfsepv.dll"        ' "u2fsepv.dll" for 32bit
        FormatType = crUXFCommaSeparatedType
    Case 6    ' Tab Separated Values
        FormatDLLName = "uxfsepv.dll"        ' "u2fsepv.dll" for 32bit
        FormatType = crUXFTabSeparatedType
    Case 7    ' Character Separated Values
        FormatDLLName = "uxfsepv.dll"        ' "u2fsepv.dll" for 32bit
        FormatType = crUXFCharSeparatedType
    Case 8    ' Text
        FormatDLLName = "uxftext.dll"        ' "u2ftext.dll" for 32bit
        FormatType = crUXFTextType
    Case 9    ' Paginated Text
        FormatDLLName = "uxftext.dll"        ' "u2ftext.dll" for 32bit
        FormatType = crUXFPaginatedTextType
    Case 10    ' Tab Separated Text
        FormatDLLName = "uxftext.dll"        ' "u2ftext.dll" for 32bit
        FormatType = crUXFTabbedTextType
    Case 11    ' Lotus WKS
        FormatDLLName = "uxfwks.dll"        ' "u2fwks.dll" for 32bit
        FormatType = crUXFLotusWksType
    Case 12    ' Lotus WK1
        FormatDLLName = "uxfwks.dll"        ' "u2fwks.dll" for 32bit
        FormatType = crUXFLotusWk1Type
    Case 13    ' Lotus WK3
        FormatDLLName = "uxfwks.dll"        ' "u2fwks.dll" for 32bit
        FormatType = crUXFLotusWk3Type
    Case 14    ' Excel 2.1
        FormatDLLName = "uxfxls.dll"        ' "u2fxls.dll" for 32bit
        FormatType = crUXFXls2Type
    Case 15    ' Excel 3.0
        FormatDLLName = "uxfxls.dll"        ' "u2fxls.dll" for 32bit
        FormatType = crUXFXls3Type
    Case 16    ' Excel 4.0
        FormatDLLName = "uxfxls.dll"        ' "u2fxls.dll" for 32bit
        FormatType = crUXFXls4Type
    Case 17    ' Excel 5.0
        FormatDLLName = "uxfxls.dll"        ' "u2fxls.dll" for 32bit
        FormatType = crUXFXls5Type
    Case 18    ' ODBC
    Case 19    ' HTML 3.0 Draft Standard
        FormatType = crUXFHTML3Type
    Case 20    ' HTML 3.0 Explorer 2.0
        FormatType = crUXFExplorer2Type
    Case 21    ' HTML 3.0 Netscape 2.0
        FormatType = crUXFNetscape2Type
End Select
' Set export options based on selected export destination
Select Case ExportFormat
    Case 0 To 17
    Select Case ExportDestination
        Case 0        ' Export to File
            Result =    crPEExportToDisk(MainJob, "c:\myfilename" & 
vbNullChar,
    FormatDLLName & vbNullChar, FormatType, UseSameNumberFormat,
    UseSameDateFormat, StringDelimiter & vbNullChar, FieldDelimiter &
    vbNullChar)
        Case 1        ' Export to MAPI
            Result =    crPEExportToMapi(MainJob, MAPIToList & vbNullChar, 
MAPICCList &
    vbNullChar, MAPISubject & vbNullChar, MAPIMessage & vbNullChar,
    FormatDLLName & vbNullChar, FormatType, UseSameNumberFormat,
    UseSameDateFormat, StringDelimiter & vbNullChar, FieldDelimiter &
    vbNullChar)
        Case 2        ' Export to Exchange
            Result =    crPEExportToExch(MainJob, EXCHProfile & vbNullChar, 
EXCHPassword
    & vbNullChar, EXCHFolderPath & vbNullChar, FormatDLLName &
    vbNullChar, FormatType, UseSameNumberFormat, UseSameDateFormat,
    StringDelimiter & vbNullChar, FieldDelimiter & vbNullChar)
    End Select
    Case 18        ' Export to ODBC
        Result =    crPEExportToODBC(MainJob, DataSource & vbNullChar, 
UserID &
    vbNullChar, Password & vbNullChar, TableName & vbNullChar)
    Case 19 To 21    ' Export to HTML
        Result =    crPEExportToHTML(MainJob, FormatType, "c:\myfilename" &
    vbNullChar)
End Select
' Start the print job
Result = PEStartPrintJob(MainJob, 1)
' Close the print job
PEClosePrintJob (MainJob)
' Close the engine
PECloseEngine


Seagate Software IMG Holdings, Inc.
http://www.seagatesoftware.com
Support services:
http://support.seagatesoftware.com